{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Hard\n",
    "You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema. Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "When creating a view in scott you must specify the schema name of the sources and the destination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE SCHEMA IF NOT EXISTS scott;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "It is decided to review the materials stock. How much did each material contribute to turnover in 2002?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>material_no</th>\n",
       "        <th>fabric</th>\n",
       "        <th>colour</th>\n",
       "        <th>pattern</th>\n",
       "        <th>quantity</th>\n",
       "        <th>cost</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Silk                </td>\n",
       "        <td>Black               </td>\n",
       "        <td>Plain               </td>\n",
       "        <td>4.90</td>\n",
       "        <td>34.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Silk                </td>\n",
       "        <td>Red Abstract        </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>9.30</td>\n",
       "        <td>93.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Yellow Stripe       </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>5.70</td>\n",
       "        <td>17.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Green Stripe        </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>2.20</td>\n",
       "        <td>6.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Black Dotted        </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>6.40</td>\n",
       "        <td>19.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Red Stripe          </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>2.20</td>\n",
       "        <td>6.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>Polyester           </td>\n",
       "        <td>Pale Yellow         </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>4.30</td>\n",
       "        <td>10.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Blue Stripe         </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>4.20</td>\n",
       "        <td>12.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Pink Check          </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>4.60</td>\n",
       "        <td>13.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Silk                </td>\n",
       "        <td>Green Abstract      </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>8.30</td>\n",
       "        <td>124.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Rayon               </td>\n",
       "        <td>Red/Orange          </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>9.70</td>\n",
       "        <td>38.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Serge               </td>\n",
       "        <td>Navy Blue           </td>\n",
       "        <td>Woven               </td>\n",
       "        <td>4.50</td>\n",
       "        <td>24.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Blue Abstract       </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>4.60</td>\n",
       "        <td>16.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Cotton              </td>\n",
       "        <td>Green Abstract      </td>\n",
       "        <td>Printed             </td>\n",
       "        <td>4.50</td>\n",
       "        <td>15.75</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Silk                ', 'Black               ', 'Plain               ', Decimal('4.90'), Decimal('34.30')),\n",
       " (2, 'Silk                ', 'Red Abstract        ', 'Printed             ', Decimal('9.30'), Decimal('93.00')),\n",
       " (3, 'Cotton              ', 'Yellow Stripe       ', 'Woven               ', Decimal('5.70'), Decimal('17.10')),\n",
       " (4, 'Cotton              ', 'Green Stripe        ', 'Woven               ', Decimal('2.20'), Decimal('6.60')),\n",
       " (5, 'Cotton              ', 'Black Dotted        ', 'Woven               ', Decimal('6.40'), Decimal('19.20')),\n",
       " (6, 'Cotton              ', 'Red Stripe          ', 'Woven               ', Decimal('2.20'), Decimal('6.60')),\n",
       " (7, 'Polyester           ', 'Pale Yellow         ', 'Printed             ', Decimal('4.30'), Decimal('10.97')),\n",
       " (8, 'Cotton              ', 'Blue Stripe         ', 'Woven               ', Decimal('4.20'), Decimal('12.60')),\n",
       " (9, 'Cotton              ', 'Pink Check          ', 'Woven               ', Decimal('4.60'), Decimal('13.80')),\n",
       " (10, 'Silk                ', 'Green Abstract      ', 'Printed             ', Decimal('8.30'), Decimal('124.50')),\n",
       " (11, 'Rayon               ', 'Red/Orange          ', 'Printed             ', Decimal('9.70'), Decimal('38.80')),\n",
       " (12, 'Serge               ', 'Navy Blue           ', 'Woven               ', Decimal('4.50'), Decimal('24.75')),\n",
       " (13, 'Cotton              ', 'Blue Abstract       ', 'Printed             ', Decimal('4.60'), Decimal('16.10')),\n",
       " (14, 'Cotton              ', 'Green Abstract      ', 'Printed             ', Decimal('4.50'), Decimal('15.75'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT material_no, fabric, colour, pattern, ROUND(SUM(quantity)::NUMERIC, 2) AS quantity,\n",
    "  ROUND(SUM(cost*quantity)::NUMERIC, 2) AS cost\n",
    "    FROM material JOIN order_line ON (order_line.ol_material=material.material_no)\n",
    "      JOIN quantities ON (quantities.size_q=order_line.ol_size AND\n",
    "                          quantities.style_q=order_line.ol_style) JOIN\n",
    "        dress_order ON (dress_order.order_no=order_line.order_ref)\n",
    "        WHERE EXTRACT(YEAR FROM order_date)=2002\n",
    "        GROUP BY material_no;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "An order for shorts has just been placed and the work is to be distributed amongst the workforce, and we wish to know how busy the shorts makers are. For each of the workers who have experience of making shorts show the number of hours work that she is currently committed to, assuming a meagre wage of £4.50 per hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>d_name</th>\n",
       "        <th>hrs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miss Pins           </td>\n",
       "        <td>28.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Miss Stitch         </td>\n",
       "        <td>49.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Needles          </td>\n",
       "        <td>18.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Seam             </td>\n",
       "        <td>28.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Taylor           </td>\n",
       "        <td>18.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms Sew              </td>\n",
       "        <td>18.61</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Miss Pins           ', Decimal('28.17')),\n",
       " ('Miss Stitch         ', Decimal('49.17')),\n",
       " ('Mr Needles          ', Decimal('18.61')),\n",
       " ('Mr Seam             ', Decimal('28.17')),\n",
       " ('Mr Taylor           ', Decimal('18.61')),\n",
       " ('Ms Sew              ', Decimal('18.61'))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT DISTINCT d_no\n",
    "      FROM dressmaker JOIN construction ON (dressmaker.d_no=construction.maker) JOIN\n",
    "        dress_order ON (dress_order.order_no=construction.order_ref) JOIN\n",
    "        order_line ON (order_line.order_ref=dress_order.order_no) JOIN\n",
    "        garment ON (garment.style_no=order_line.ol_style)\n",
    "        WHERE LOWER(garment.description)='shorts'\n",
    ")\n",
    "SELECT d_name, ROUND(SUM(labour_cost)::NUMERIC/4.5, 2) hrs\n",
    "  FROM dressmaker JOIN construction ON (dressmaker.d_no=construction.maker) JOIN\n",
    "    dress_order ON (dress_order.order_no=construction.order_ref) JOIN\n",
    "    order_line ON (order_line.order_ref=dress_order.order_no) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style) JOIN\n",
    "    quantities ON (quantities.style_q=order_line.ol_style AND\n",
    "                   quantities.size_q=order_line.ol_size)\n",
    "    WHERE completed='N' AND d_no IN (SELECT * FROM t)\n",
    "    GROUP BY d_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "\"Big spender of the year\" is the customer who spends the most on high value items. Identify the \"Big spender of the year 2002\" if the \"high value\" threshold is set at £30. Also who would it be if the threshold was £20 or £50?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>c_name</th>\n",
       "        <th>thres</th>\n",
       "        <th>spending</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Brass            </td>\n",
       "        <td>50</td>\n",
       "        <td>72.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mr Brass            </td>\n",
       "        <td>20</td>\n",
       "        <td>198.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ms White            </td>\n",
       "        <td>30</td>\n",
       "        <td>173.55</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mr Brass            ', 50, Decimal('72.00')),\n",
       " ('Mr Brass            ', 20, Decimal('198.54')),\n",
       " ('Ms White            ', 30, Decimal('173.55'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT order_ref, line_no, quantity*cost+labour_cost tot_cost\n",
    "    FROM order_line JOIN quantities ON (order_line.ol_style=quantities.style_q\n",
    "                                        AND order_line.ol_size=quantities.size_q) JOIN\n",
    "    garment ON (order_line.ol_style=garment.style_no) JOIN\n",
    "    material ON (material.material_no=order_line.ol_material)\n",
    ")\n",
    "SELECT c_name, thres, ROUND(cost::NUMERIC, 2) spending FROM\n",
    "((SELECT c_name, 20 AS thres, SUM(tot_cost) AS cost\n",
    "  FROM (SELECT * FROM t WHERE tot_cost>=20) t JOIN dress_order ON (\n",
    "      t.order_ref=dress_order.order_no) JOIN\n",
    "    jmcust ON (jmcust.c_no=dress_order.cust_no)\n",
    "    GROUP BY c_name\n",
    "    ORDER BY cost DESC\n",
    "    LIMIT 1)\n",
    "UNION\n",
    "(SELECT c_name, 30 AS thres, SUM(tot_cost) AS cost\n",
    "  FROM (SELECT * FROM t WHERE tot_cost>=30) t JOIN dress_order ON (\n",
    "      t.order_ref=dress_order.order_no) JOIN\n",
    "    jmcust ON (jmcust.c_no=dress_order.cust_no)\n",
    "    GROUP BY c_name\n",
    "    ORDER BY cost DESC\n",
    "    LIMIT 1)\n",
    "UNION\n",
    "(SELECT c_name, 50 AS thres, SUM(tot_cost) AS cost\n",
    "  FROM (SELECT * FROM t WHERE tot_cost>=50) t JOIN dress_order ON (\n",
    "      t.order_ref=dress_order.order_no) JOIN\n",
    "    jmcust ON (jmcust.c_no=dress_order.cust_no)\n",
    "    GROUP BY c_name\n",
    "    ORDER BY cost DESC\n",
    "    LIMIT 1)) a;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Who is the fastest at making trousers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>d_no</th>\n",
       "        <th>d_name</th>\n",
       "        <th>days</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Mr Needles          </td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 'Mr Needles          ', 3)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT d_no, d_name, finish_date-start_date days\n",
    "  FROM dressmaker JOIN construction ON (dressmaker.d_no=construction.maker) JOIN\n",
    "    order_line ON (order_line.order_ref=construction.order_ref AND\n",
    "                   order_line.line_no=construction.line_ref) JOIN\n",
    "    garment ON (garment.style_no=order_line.ol_style)\n",
    "    WHERE LOWER(description)='trousers'\n",
    "    ORDER BY days\n",
    "    LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "\"Employee of the month\" is the seamstress who completes the greatest value of clothes. Show the \"employees of the month\" for months in 2002."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>month</th>\n",
       "        <th>d_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Miss Stitch         </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Mrs Hem             </td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Miss Stitch         </td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Miss Stitch         '),\n",
       " (2, 'Mrs Hem             '),\n",
       " (3, 'Miss Stitch         ')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT d_name, EXTRACT(MONTH FROM start_date)::INTEGER AS month, ROUND(SUM(quantity*cost+labour_cost)::NUMERIC, 2) val,\n",
    "    RANK() OVER(PARTITION BY EXTRACT(MONTH FROM start_date) ORDER BY SUM(quantity*cost+labour_cost) DESC) AS rank\n",
    "    FROM material JOIN order_line ON (order_line.ol_material=material.material_no)\n",
    "      JOIN quantities ON (quantities.size_q=order_line.ol_size AND\n",
    "                          quantities.style_q=order_line.ol_style) JOIN\n",
    "        garment ON (garment.style_no=order_line.ol_style) JOIN\n",
    "        construction ON (order_line.order_ref=construction.order_ref AND\n",
    "                         order_line.line_no=construction.line_ref) JOIN\n",
    "        dressmaker ON (dressmaker.d_no=construction.maker)\n",
    "        WHERE EXTRACT(YEAR FROM start_date)=2002\n",
    "        GROUP BY d_name, EXTRACT(MONTH FROM start_date)\n",
    ")\n",
    "SELECT month, d_name FROM t WHERE rank=1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
